window関数で重複しているときに特定カラムの値に優先度をつけて並べ替えたい

データアナリティクス事業本部の荒木です。

先日RedshiftでDM作成する際に、window関数を使って少し複雑な順位付けを行ったのでまとめたいと思います。

本題

前提条件

今回実装した要件は以下です。

• とあるデータで、カラムA,B,C,Dをキーとして重複するデータが存在する
• 重複するデータが存在する場合、カラムtypeの値を条件に1～3の優先順位を付けたい
• カラムtypeの値は、優先順位1、優先順位2、優先順位3がありその値によって優先度を付与する
• 重複していないデータの優先順位は0にする

実装内容

テーブルのサンプルとSQLは以下となります。

元テーブル（test.raw_table）

A	B	C	D	E	type
AAA	AAa	Aaa	aaa	123	'優先順位1'
AAA	AAa	Aaa	BBB	456	'優先順位2'
BBB	BBb	Bbb	bbb	789	'優先順位1'
BBB	BBb	Bbb	bbb	012	'優先順位2'
BBB	BBb	Bbb	bbb	345	'優先順位3'
CCC	CCc	Ccc	DDD	678	'優先順位1'
CCC	CCc	Ccc	DDD	901	'優先順位3'

作成したいデータマート（test.priority_table）

A	B	C	D	E	type	priority
AAA	AAa	Aaa	aaa	123	'優先順位1'	0
AAA	AAa	Aaa	BBB	456	'優先順位2'	0
BBB	BBb	Bbb	bbb	789	'優先順位1'	1
BBB	BBb	Bbb	bbb	012	'優先順位2'	2
BBB	BBb	Bbb	bbb	345	'優先順位3'	3
CCC	CCc	Ccc	DDD	678	'優先順位1'	1
CCC	CCc	Ccc	DDD	901	'優先順位3'	2

SQLコード

CREATE temp TABLE tmp_priority AS
WITH 
duplicate_counts AS (
    SELECT 
        A
      , B
      , C
      , D
      , COUNT(*) AS dup_num
    FROM test.raw_table
    GROUP BY 1,2,3,4
),
temp_priority AS (
    SELECT 
        A
      , B
      , C
      , D
      , type
      , ROW_NUMBER() OVER (
        PARTITION BY A, B, C, D
        ORDER BY 
            CASE type
                WHEN '優先順位1' THEN 1
                WHEN '優先順位2' THEN 2
                WHEN '優先順位3' THEN 3
            END) as rank
    FROM test.raw_table
)
SELECT 
    A
  , B
  , C
  , D
  , tp.type
  , CASE WHEN dc.dup_num > 1 THEN
        ROW_NUMBER() OVER (
        PARTITION BY tp.A, tp.B, tp.C, tp.D
        ORDER BY rank
        ) 
        ELSE 0
    END AS priority
FROM temp_priority tp
    LEFT JOIN duplicate_counts dc
        ON tp.A = dc.A
            AND tp.B = dc.B
            AND tp.C = dc.C
            AND tp.D = dc.D
;

INSERT INTO test.priority_table
SELECT
    rt.A
  , rt.B
  , rt.C
  , rt.D
  , rt.E
  , rt.type
  , tp.priority
from test.raw_table rt
    LEFT JOIN tmp_priority tp
        ON rt.A = tp.A
            AND rt.B = tp.B
            AND rt.C = tp.C
            AND rt.D = tp.D
            AND rt.type = tp.type
;

詳細

• duplicate_countsはpriorityを付与する際に、重複がないものは0で重複があるものは1~3の数字を付与する要件になっていました。window関数で付与した順位は1から順位付けされます。重複があるレコードとないレコードに順位付けされた1を区別し、重複がないレコードを0にするのが難しかったため、重複があるレコードをカウントし判断するためのサブクエリとなります。後に結合し、重複がないレコードの順位は1から0に変更する処理で使用します。
• temp_priorityではwindow関数で、カラムtypeの値を条件によって優先順位付けします。
• 優先順位付けのみだと、仮に重複したデータに優先順位2が存在しない場合、順位が連番でなくなってしまいます。そのため、再度window関数にてその優先順位を順位付けすることで連番で順位付けしています。

追記（2023/06/29）

window関数のcountを使ってもっと簡潔に結合なしで書くことできたので追記します。

INSERT INTO bpi.brand_market_data
WITH temp_priority AS (
    SELECT 
        A
      , B
      , C
      , D
      , type
      , COUNT(*) OVER (
        PARTITION BY A, B, C, D
        ) as dup_num
      , ROW_NUMBER() OVER (
        PARTITION BY A, B, C, D
        ORDER BY 
            CASE type
                WHEN '優先順位1' THEN 1
                WHEN '優先順位2' THEN 2
                WHEN '優先順位3' THEN 3
            END) as rank
    FROM test.raw_table
)
SELECT 
    A
  , B
  , C
  , D
  , type
  , CASE WHEN dup_num > 1 THEN
        ROW_NUMBER() OVER (
        PARTITION BY A, B, C, D
        ORDER BY rank
        ) 
        ELSE 0
    END AS priority
FROM temp_priority
;

まとめ

最初、この処理をwindow関数1回で実装しようとしたり、色々試して複雑なSQLを書いてしまったりしてしまっていましたが、window関数を2回使うことですっきりとしたわかりやすいSQLを書くことができました。 window関数は、重複するレコードに対して何か処理をしたいときには便利だと思いました。

どなたかのお役に立つ情報になれば幸いです。

参照

Window 関数